package com.zx.dao.impl;

import com.zx.bean.Express;

import com.zx.dao.ExpressDao;


import com.zx.exception.DuplicateCodeException;
import com.zx.util.DruidUtil;


import java.sql.*;
import java.util.*;

/**
 * @Author CaesarChang张旭
 * @Date 2020/10/21  8:32 上午
 * @Version 1.0
 */
public class ExpressDaoImpl implements ExpressDao {
    /**
     * 先写SQL语句
     * @return
     */
    private  static  final String  SQL_CONSOLE="select count(id) data1_size,count(to_days(intime)=to_days(now()) and status=1 or null) data1_day," +
            "count(status=0 or null) data2_size,count(to_days(intime)=to_days(now()) and status=0 or null) data2_day " +
            "from express;";
    private  static  final String  SQL_FIND_ALL="select * from express"; //查询全部
    private  static  final String  SQL_FIND_LIMIT="select * from express limit ?,?";  //分页查询全部
    private  static  final String  SQL_FIND_BY_CODE="select * from express where code=?";
    private  static  final String  SQL_FIND_BY_NUMBER="select * from express where number=?";
    private  static  final String  SQL_FIND_BY_SYSPHONE="select * from express where sysphone=?";
    private  static  final String  SQL_USERPHONE="select * from express where userphone=?";
    private  static  final String  SQL_INSERT="insert into express (number,username,userphone,company,code,intime,status,sysphone) values(?,?,?,?,?,now(),0,?)";
    private  static  final String  SQL_UPDATE="update express set number=?,username=?,company=?,status=? where id=?";
    private  static  final String  SQL_DELETE="delete from express where id=?";
    private  static  final String  SQL_UPDATE_STATUS="update express set status=1,outtime=now(),code=-1 where code=?";

    @Override
    public List<Map<String, Integer>> console() {
        //1 获取数据库连接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;
        ArrayList<Map<String ,Integer>> data=new ArrayList<>();

        try {
            //2预编译SQL
            preparedStatement=connection.prepareStatement(SQL_CONSOLE);
            //3 填充参数

            //4 执行SQL
            resultSet=preparedStatement.executeQuery();
            //5 获取执行结果
            if(resultSet.next()){
                int data1_size = resultSet.getInt("data1_size");
                int data1_day = resultSet.getInt("data1_day");
                int data2_size = resultSet.getInt("data2_size");
                int data2_day = resultSet.getInt("data2_day");
                Map data1=new HashMap();
                data1.put("data1_size",data1_size);
                data1.put("data1_day",data1_day);
                Map data2=new HashMap();
                data2.put("data2_size",data2_size);
                data2.put("data2_day",data2_day);
                data.add(data1);
                data.add(data2);
            }
            //6 释放资源
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement, resultSet);
        }

        return data;
    }

    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;
        List<Express> data=new ArrayList<>();

        try {
            //2预编译SQL
            if(limit){
                 preparedStatement = connection.prepareStatement(SQL_FIND_LIMIT);
                //3 填充参数
                preparedStatement.setInt(1,offset);
                preparedStatement.setInt(2,pageNumber);

            }else{
                preparedStatement=connection.prepareStatement(SQL_FIND_ALL);

            }


            //4 执行SQL
            resultSet=preparedStatement.executeQuery();
            //5 获取执行结果
                while(resultSet.next()){
                    int id=resultSet.getInt("id");
                    String number=resultSet.getString("number");
                    String username=resultSet.getString("username");
                    String userphone=resultSet.getString("userphone");
                    String company=resultSet.getString("company");
                    String code=resultSet.getString("code");
                    Timestamp intime=resultSet.getTimestamp("intime");
                    Timestamp outtime=resultSet.getTimestamp("outtime");
                    int status=resultSet.getInt("status");
                    String sysPhone=resultSet.getString("sysPhone");
                    Express e=new Express(id,number,username,userphone,company,code,intime,outtime,status,sysPhone);
                    data.add(e);
                }
            //6 释放资源
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement, resultSet);
        }
        return data;
    }

    @Override
    public Express findByNumber(String number) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;


        try {
            //2预编译SQL
            preparedStatement=connection.prepareStatement(SQL_FIND_BY_NUMBER);
            //3 填充参数
            preparedStatement.setString(1,number);
            //4 执行SQL
            resultSet=preparedStatement.executeQuery();
            //5 获取执行结果
            if(resultSet.next()){
                int id=resultSet.getInt("id");
                String username=resultSet.getString("username");
                String userphone=resultSet.getString("userphone");
                String company=resultSet.getString("company");
                String code=resultSet.getString("code");
                Timestamp intime=resultSet.getTimestamp("intime");
                Timestamp outtime=resultSet.getTimestamp("outtime");
                int status=resultSet.getInt("status");
                String sysPhone=resultSet.getString("sysPhone");
                Express e=new Express(id,number,username,userphone,company,code,intime,outtime,status,sysPhone);
               return e;
            }
            //6 释放资源
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement, resultSet);
        }
        return null;
    }

    @Override
    public Express findByCode(String code) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;


        try {
            //2预编译SQL
            preparedStatement=connection.prepareStatement(SQL_FIND_BY_CODE);
            //3 填充参数
                preparedStatement.setString(1,code);
            //4 执行SQL
            resultSet=preparedStatement.executeQuery();
            //5 获取执行结果
            if(resultSet.next()){
                int id=resultSet.getInt("id");
                String userphone=resultSet.getString("userphone");
                String number=resultSet.getString("number");
                String username=resultSet.getString("username");
                String company=resultSet.getString("company");
                Timestamp intime=resultSet.getTimestamp("intime");
                Timestamp outtime=resultSet.getTimestamp("outtime");
                int status=resultSet.getInt("status");
                String sysPhone=resultSet.getString("sysPhone");
                Express e=new Express(id,number,username,userphone,company,code,intime,outtime,status,sysPhone);
                return e;
            }
            //6 释放资源
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement, resultSet);
        }
        return null;
    }

    @Override
    public List<Express> findByUserPhone(String userPhone) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;
        ArrayList<Express> data=new ArrayList<>();


        try {
            //2预编译SQL
            preparedStatement=connection.prepareStatement(SQL_USERPHONE);
            //3 填充参数
            preparedStatement.setString(1,userPhone);
            //4 执行SQL
            resultSet=preparedStatement.executeQuery();
            //5 获取执行结果
            while(resultSet.next()){
                int id=resultSet.getInt("id");
                String number=resultSet.getString("number");
                String username=resultSet.getString("username");
                String company=resultSet.getString("company");
                String code=resultSet.getString("code");
                Timestamp intime=resultSet.getTimestamp("intime");
                Timestamp outtime=resultSet.getTimestamp("outtime");
                int status=resultSet.getInt("status");
                String sysPhone=resultSet.getString("sysPhone");
                Express e=new Express(id,number,username,userPhone,company,code,intime,outtime,status,sysPhone);
                data.add(e);
            }
            //6 释放资源
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement, resultSet);
        }
        return data;
    }

    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;
        ArrayList<Express> data=new ArrayList<>();



        try {
            //2预编译SQL
            preparedStatement=connection.prepareStatement(SQL_FIND_BY_SYSPHONE);
            //3 填充参数
            preparedStatement.setString(1,sysPhone);
            //4 执行SQL
            resultSet=preparedStatement.executeQuery();
            //5 获取执行结果
            while(resultSet.next()){
                int id=resultSet.getInt("id");
                String number=resultSet.getString("number");
                String username=resultSet.getString("username");
                String userphone=resultSet.getString("userphone");
                String company=resultSet.getString("company");
                String code=resultSet.getString("code");
                Timestamp intime=resultSet.getTimestamp("intime");
                Timestamp outtime=resultSet.getTimestamp("outtime");
                int status=resultSet.getInt("status");
                Express e=new Express(id,number,username,userphone,company,code,intime,outtime,status,sysPhone);
                data.add(e);
            }
            //6 释放资源
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement, resultSet);
        }
        return data;
    }
//insert into express (number,username,userphone,company,code,intime,status,sysphone) values(?,?,?,?,?,now(),0,?)"
    @Override
    public boolean insert(Express e) throws DuplicateCodeException{
        //1 连接的获取
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement =null;

        try {
            //2预编译SQL
            preparedStatement=connection.prepareStatement(SQL_INSERT);
            //3 填充参数
            preparedStatement.setString(1,e.getNumber());
            preparedStatement.setString(2,e.getUsername());
            preparedStatement.setString(3,e.getUserphone());
            preparedStatement.setString(4,e.getCompany());
            preparedStatement.setString(5,e.getCode());
            preparedStatement.setString(6,e.getSysPhone());
            //4 执行SQL获取执行结果
            return  preparedStatement.executeUpdate()>0?true:false;
            //6 释放资源
        } catch (SQLException e1) {
            if(e1.getMessage().endsWith("for key 'code'")){
                DuplicateCodeException e2=new DuplicateCodeException(e1.getMessage());
                throw e2;
            }else{
                e1.printStackTrace();
            }

        } finally {
            DruidUtil.close(connection,preparedStatement, null);
        }

        return false;
    }
//update express set number=?,username=?,company=?,status=? where id=?
    @Override
    public boolean update(int id, Express newExpress) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement =null;


        try {
            //2预编译SQL
            preparedStatement=connection.prepareStatement(SQL_UPDATE);
            //3 填充参数
            preparedStatement.setString(1, newExpress.getNumber());
            preparedStatement.setString(2, newExpress.getUsername());
            preparedStatement.setString(3,newExpress.getCompany());
            preparedStatement.setInt(4,newExpress.getStatus());
            preparedStatement.setInt(5,id);
            int i = preparedStatement.executeUpdate();
            //4 执行SQL
            return i>0?true:false;


            //6 释放资源
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement, null);
        }
        return false;
    }

    @Override
    public boolean delete(int id) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement =null;


        try {
            //2预编译SQL
            preparedStatement=connection.prepareStatement(SQL_DELETE);
            //3 填充参数
            preparedStatement.setInt(1,id);
            //4 执行SQL 获取执行结果
            return preparedStatement.executeUpdate()>0?true:false;


            //6 释放资源
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement, null);
        }
        return false;
    }

    //update express set status=1 where code=?
    @Override
    public boolean updateStatus(String code) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement =null;
        ResultSet resultSet =null;

        try {
            //2预编译SQL
            preparedStatement=connection.prepareStatement(SQL_UPDATE_STATUS);
            //3 填充参数
            preparedStatement.setString(1,code);

            //4 执行SQL
            return preparedStatement.executeUpdate()>0?true:false;
            //5 获取执行结果

            //6 释放资源
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement, null);
        }
        return false;
    }
}
